<!DOCTYPE html>
<html class="client-nojs vector-feature-language-in-header-enabled vector-feature-language-in-main-page-header-disabled vector-feature-page-tools-pinned-disabled vector-feature-toc-pinned-clientpref-0 vector-toc-not-available vector-feature-main-menu-pinned-disabled vector-feature-limited-width-clientpref-1 vector-feature-limited-width-content-enabled vector-feature-custom-font-size-clientpref-1 vector-feature-appearance-pinned-clientpref-0 vector-feature-night-mode-enabled skin-theme-clientpref-os vector-sticky-header-enabled" lang="fr" dir="ltr"><head>
<meta charset="UTF-8">
<title>Merge (SQL)</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="icon" type="image/png" href="./_res_/favicon.png">
<link rel="canonical" href="https://fr.wikipedia.org/wiki/Merge_(SQL)"> <link href="./_mw_/ext.pygments.css" rel="stylesheet" type="text/css">
<link href="./_mw_/ext.wikimediamessages.styles.css" rel="stylesheet" type="text/css">
<link href="./_mw_/skins.vector.icons.css" rel="stylesheet" type="text/css">
<link href="./_mw_/skins.vector.search.codex.styles.css" rel="stylesheet" type="text/css">
<link href="./_mw_/skins.vector.styles.css" rel="stylesheet" type="text/css">
<meta name="ResourceLoaderDynamicStyles" content="">
<link rel="stylesheet" type="text/css" href="./_mw_/site.styles.css">
<link rel="stylesheet" type="text/css" href="./_mw_/noscript.css">
<link rel="stylesheet" type="text/css" href="./_res_/footer.css">
<link rel="stylesheet" type="text/css" href="./_res_/vector-2022.css">
</head>
<body class="skin--responsive skin-vector skin-vector-search-vue mediawiki ltr sitedir-ltr mw-hide-empty-elt ns-0 ns-subject page-Merge_SQL rootpage-Merge_SQL skin-vector-2022 action-view">
<div class="mw-page-container">
<div class="mw-page-container-inner">
<div class="mw-content-container">
<main id="content" class="mw-body">
<header class="mw-body-header vector-page-titlebar">
<h1 id="firstHeading" class="firstHeading mw-first-heading"><span class="mw-page-title-main">Merge (SQL)</span></h1>
</header>
<a id="top"></a>
<div id="bodyContent" class="vector-body ve-init-mw-desktopArticleTarget-targetContainer" aria-labelledby="firstHeading" data-mw-ve-target-container="">
<div id="contentSub">
<div id="mw-content-subtitle"></div>
</div>
<div id="mw-content-text" class="mw-body-content mw-content-ltr" lang="fr" dir="ltr"><div class="mw-content-ltr mw-parser-output" lang="fr" dir="ltr">
<p>La commande <i><b>Merge</b></i> en <a href="Structured_Query_Language" title="Structured Query Language">SQL</a> est une instruction qui permet de mettre à jour ou insérer un ou plusieurs <a href="Tuple" class="mw-redirect" title="Tuple">tuples</a> dans une <a href="Table_(base_de_donn%C3%A9es)" title="Table (base de données)">table</a> (ou <a href="Vue_(base_de_donn%C3%A9es)" title="Vue (base de données)">vue</a>) d'une <a href="Base_de_donn%C3%A9es_relationnelle" title="Base de données relationnelle">base de données relationnelle</a> à partir de données sources, selon des conditions. Cette commande a été rajoutée dans le standard SQL:2003, et étendue dans le standard <a href="SQL%3A2008" title="SQL:2008">SQL:2008</a>, et permet d'éviter des combinaisons d’instructions <a href="Insert_(SQL)" title="Insert (SQL)"><i>Insert</i></a>, <i>Update</i> et <i>Delete</i> sur la table cible.
</p><p><br>
</p>
<div class="mw-heading mw-heading2"><h2 id="Syntaxe">Syntaxe</h2></div>
<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="n">MERGE</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">table_cible</span><span class="w"> </span><span class="n">T</span><span class="w"> </span><span class="c1">-- table à modifier</span>
<span class="w"> </span><span class="k">USING</span><span class="w"> </span><span class="n">données_source</span><span class="w"> </span><span class="n">S</span><span class="w"> </span><span class="c1">-- Listes des données sources</span>
<span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="p">(</span><span class="n">Conditions1</span><span class="p">)</span><span class="w"> </span><span class="c1">-- Condition de correspondance/jointure</span>
<span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">MATCHED</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="c1">-- Si vrai</span>
<span class="w"> </span><span class="k">UPDATE</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">colonne1</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">valeur1</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">colonne2</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">valeur2</span><span class="w"> </span><span class="p">...]</span><span class="w"> </span><span class="c1">---- Mise à jour</span>
<span class="w"> </span><span class="k">DELETE</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="p">(</span><span class="n">Conditions2</span><span class="p">)</span><span class="w"> </span><span class="c1">---- ou Suppression sous condition</span>
<span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="n">MATCHED</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="c1">-- Si faux</span>
<span class="w"> </span><span class="k">INSERT</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">liste_colonnes</span><span class="p">)</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">liste_valeurs</span><span class="p">)</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="err">}</span><span class="w"> </span><span class="c1">---- Ajout</span>
</pre></div>
<p>Une <a href="Jointure_(informatique)" title="Jointure (informatique)">jointure</a> droite externe (<i>Right outer join</i>) est effectuée entre la cible et la source. Si plusieurs tuples de la source correspondent à un même tuple de la cible alors une erreur est levée.
Dans la syntaxe, Conditions1 représente la condition de jointure qui permet de définir si un tuple cible doit être ajouté ou mise à jour (ou supprimée si on est dans le cas de la Conditions2).
Cela signifie que si un tuple source correspond selon la condition de jointure à un tuple de la table cible alors les instructions dans <i>WHEN MATCHED THEN</i> sont effectuées. Si un tuple source ne correspond pas à la condition de jointure alors les instructions dans <i>WHEN NOT MATCHED THEN</i> sont effectuées.
</p><p>Les données sources peuvent provenir d'une table, d'une vue ou d'une sous <a href="Select_(SQL)" title="Select (SQL)">requête</a>.
</p>
<div class="mw-heading mw-heading2"><h2 id="Notes_et_références"><span id="Notes_et_r.C3.A9f.C3.A9rences"></span>Notes et références</h2></div>
<div class="references-small decimal" style="">
</div>
<ul><li><abbr class="abbr indicateur-langue" title="Langue : anglais">(en)</abbr> Cet article est partiellement ou en totalité issu de l’article de Wikipédia en anglais intitulé <span class="">« <a class="external text" href="https://en.wikipedia.org/wiki/Merge_(SQL)?oldid=581458759">Merge (SQL)</a> » <small>(<a class="external text" href="https://en.wikipedia.org/wiki/Merge_(SQL)?action=history">voir la liste des auteurs</a>)</small></span>.</li></ul>
<div class="mw-heading mw-heading2"><h2 id="Liens_externes">Liens externes</h2></div>
<ul><li><a rel="nofollow" class="external text" href="http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_merge.dita">Documentation du Merge</a> pour <a href="IBM_DB2" class="mw-redirect" title="IBM DB2">DB2 z/OS</a></li>
<li><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/E18283_01/timesten.112/e13070/state.htm#insertedID46">Documentation du <i><b>Merge</b></i></a> d'<a href="Oracle_Database" title="Oracle Database">Oracle</a> 11g R2</li>
<li><a rel="nofollow" class="external text" href="http://msdn.microsoft.com/en-us/library/bb510625.aspx">Documentation du <i><b>Merge</b></i></a> dans <a href="Microsoft_SQL_Server" title="Microsoft SQL Server">SQL Server</a> 2012</li></ul>
<div class="navbox-container" style="clear:both;">
</div>
<ul id="bandeau-portail" class="bandeau-portail"><li><span class="bandeau-portail-element"><span class="bandeau-portail-icone"><span class="noviewer" typeof="mw:File"></span></span> <span class="bandeau-portail-texte">Portail des bases de données</span> </span></li> </ul></div><!--htdig_noindex--><div><div class="zim-footer">
Cet article est issu de <a class="external text" title="Dernière modification le 2022-12-12" href="https://fr.wikipedia.org/wiki/?title=Merge_(SQL)&oldid=199433001">Wikipédia</a>. Sauf mention contraire, le texte est disponible sous <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.fr">Creative Commons Attribution-Share Alike 4.0</a>. Des conditions supplémentaires peuvent s’appliquer aux fichiers multimédias.
</div>
</div><!--/htdig_noindex--></div>
</div>
</main>
</div>
</div>
</div>
<script src="./_webp_/webpHandler.js"></script>
</body></html>